﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace CommonUtil
{
    /// <summary>
    /// SQLite操作类
    /// </summary>
    public class SQLiteHelper
    {
        /// <summary>
        /// SqliteConn数据库连接
        /// </summary>
        public static SQLiteConnection SqliteConn = null;

        #region 属性
        /// <summary>
        /// 数据库文件路径
        /// </summary>
        private static string _DbName = String.Empty;
        /// <summary>
        /// 数据库文件路径
        /// </summary>
        public static string DbName
        {
            set { _DbName = value; }
            get { return _DbName; }
        }

        /// <summary>
        /// 数据库密码
        /// </summary>
        private static string _DbPwd = String.Empty;
        /// <summary>
        /// 数据库密码
        /// </summary>
        public static string DbPwd
        {
            set { _DbPwd = value; }
            get { return _DbPwd; }
        }

        /// <summary>
        /// 获得连接对象
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection Connection
        {
            get
            {
                if (SqliteConn == null)
                {
                    if (String.IsNullOrEmpty(_DbName))
                    {
                        _DbName = "YzSoft.db";
                    }

                    string DbFilePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + _DbName;

                    if (!File.Exists(DbFilePath))
                    {
                        return null;
                        throw new Exception("访问异常！数据文件不存在！");
                    }


                    if (String.IsNullOrEmpty(_DbPwd))
                    {
                        SqliteConn = new SQLiteConnection("Data Source=" + DbFilePath + ";Pooling=True;Max Pool Size=100");
                        SqliteConn.Open();
                    }
                    else
                    {
                        string connStr = "Data Source=" + DbFilePath + ";Pooling=True;Max Pool Size=100";
                        SqliteConn = new SQLiteConnection(connStr);
                        SqliteConn.SetPassword(_DbPwd);
                        SqliteConn.Open();
                    }
                }
                else if (SqliteConn != null && SqliteConn.State == ConnectionState.Closed)
                {
                    SqliteConn.Open();
                }
                else if (SqliteConn.State == ConnectionState.Broken)
                {
                    SqliteConn.Close();
                    SqliteConn.Open();
                }
                return SqliteConn;
            }
        }
        #endregion

        #region 初始化语句参数
        /// <summary>
        /// 初始化语句参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="transaction"></param>
        /// <param name="cmdText"></param>
        /// <param name="p"></param>        
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteTransaction transaction, string cmdText, SQLiteParameter[] p)
        {
            cmd.Parameters.Clear();
            cmd.Connection = Connection;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 60;

            if (transaction != null)
            {
                cmd.Transaction = transaction;
            }
            if (p != null)
            {
                AttachParameters(cmd, p);
            }
        }
        private static void AttachParameters(SQLiteCommand command, SQLiteParameter[] parameters)
        {
            foreach (SQLiteParameter param in parameters)
            {
                if ((param.Direction == ParameterDirection.InputOutput) && (param.Value == null))
                {
                    param.Value = DBNull.Value;
                }
                command.Parameters.Add(param);
            }
        }

        #endregion

        #region 创建数据库
        /// <summary>
        /// 创建没加密码的数据库
        /// </summary>
        /// <param name="dbinfo">要创建的数据库信息(数据库文件所在路径+文件名)</param>
        /// <returns>bool, 是否创建成功</returns>
        public static bool CreateDataBase(string dbinfo)
        {
            if (System.IO.File.Exists(dbinfo)) return false;
            try
            {
                SQLiteConnection.CreateFile(dbinfo);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 创建带密码数据库
        /// </summary>
        /// <param name="dbinfo">要创建的数据库信息(数据库文件所在路径+文件名)</param>
        /// <param name="passWord">密码</param>
        /// <returns>bool, 是否创建成功</returns>
        public static bool CreateDataBaseEncrypted(string dbinfo, string passWord)
        {
            bool flag = false;
            if (CreateDataBase(dbinfo))
            {
                SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbinfo);
                try
                {
                    conn.Open();
                    conn.ChangePassword(passWord);
                    flag = true;
                }
                catch (SQLiteException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        #endregion

        #region 关闭数据库连接
        /// <summary>
        /// CloseDbConn关闭数据库连接
        /// </summary>
        public static void CloseDbConn()
        {
            if (SqliteConn != null && SqliteConn.State == ConnectionState.Open) SqliteConn.Close();
        }
        #endregion

        #region 更改数据库密码
        /// <summary>
        /// 更改数据库密码
        /// </summary>
        /// <param name="newPassWord">新的数据库密码</param>
        /// <returns>返回true|false</returns>
        public static bool ChangePassWord(string newPassWord)
        {
            bool flag = false;
            try
            {
                if (SqliteConn == null)
                    SqliteConn = Connection;
                else if (SqliteConn.State != ConnectionState.Open)
                    SqliteConn.Open();

                SqliteConn.ChangePassword(newPassWord);
                flag = true;
            }
            catch (SQLiteException ex)
            {
                throw ex;
            }
            return flag;
        }
        #endregion

        #region 执行SQL返回DataTable
        /// <summary>
        /// 执行sql返回DataTable
        /// </summary>
        /// <param name="cmdText">需执行sql语句</param>
        /// <param name="p">sql的参数, 无则设置为null</param>
        /// <returns>返回DataTable</returns>
        public static DataTable ExecuteDataTable(string cmdText, SQLiteParameter[] p)
        {
            try
            {
                DataSet ds = null;
                ds = ExecuteDataset(cmdText, p);
                if (ds != null)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch (SQLiteException ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region 检查是否存在相应记录
        /// <summary>
        /// 检查是否存在相应记录
        /// </summary>
        /// <param name="cmdText">要执行的SQL语句</param>
        /// <param name="p">sql的参数, 无则设置为null</param>
        /// <returns>返回true|false</returns>
        public static bool CheckIsExist(string cmdText, SQLiteParameter[] p)
        {
            bool flag = false;
            SQLiteDataReader reader = null;
            try
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, null, cmdText, p);
                    reader = command.ExecuteReader();
                    if (reader.HasRows && reader.Read())
                    {
                        flag = true;
                    }
                }
            }
            catch (SQLiteException ex)
            {
                flag = false;
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
            return flag;
        }
        #endregion

        #region 执行SQL返回DataSet
        /// <summary>
        /// 执行SQL返回DataSet
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <param name="p">sql参数无则设置为null</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(string cmdText, SQLiteParameter[] p)
        {
            try
            {
                DataSet ds = new DataSet();
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, null, cmdText, p);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    da.Fill(ds);
                    return ds;
                }
            }
            catch (SQLiteException ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region 执行SQL返回DataRow
        /// <summary>
        /// 执行SQL返回DataRow
        /// </summary>
        /// <param name="cmdText">要执行的sql</param>
        /// <param name="p">sql的参数, 无则设置为null</param>
        /// <returns>返回DataRows</returns>
        public static DataRow ExecuteDataRow(string cmdText, SQLiteParameter[] p)
        {
            DataSet ds = ExecuteDataset(cmdText, p);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0];
            return null;
        }
        #endregion

        #region 执行SQL返回受影响的行数(返回-1则为异常)
        /// <summary>
        /// 执行update delete返回受影响的行数
        /// </summary>
        /// <param name="cmdText">要执行的sql语句</param>
        /// <param name="p">sql的参数, 无则设置为null</param>
        /// <returns>返回受影响行数(返回-1则为异常)</returns>
        public static int ExecuteNonQuery(string cmdText, SQLiteParameter[] p)
        {
            try
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, null, cmdText, p);
                    int result = command.ExecuteNonQuery();
                    return result;
                }
            }
            catch (SQLiteException ex)
            {
                return -1;
                throw ex;
            }
        }
        #endregion

        #region 执行sql返回SqlDataReader(注意关闭该SqlDataReader)
        /// <summary>
        /// 返回SqlDataReader对象(不带参数)
        /// </summary>
        /// <param name="cmdText">要执行的SQL语句</param>
        /// <returns>返回SqlDataReader(注意关闭该SqlDataReader)</returns>
        public static SQLiteDataReader ExecuteReader(string cmdText)
        {
            try
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, null, cmdText, null);
                    SQLiteDataReader reader = command.ExecuteReader();
                    return reader;
                }
            }
            catch (SQLiteException ex)
            {
                return null;
                throw ex;
            }
        }

        /// <summary>
        /// 返回SqlDataReader对象(带参数)
        /// </summary>
        /// <param name="cmdText">要执行的sql</param>
        /// <param name="p">sql的参数</param>
        /// <returns>返回SqlDataReader(注意关闭该SqlDataReader)</returns>
        public static SQLiteDataReader ExecuteReader(string cmdText, SQLiteParameter[] p)
        {
            try
            {
                SQLiteCommand command = new SQLiteCommand();
                PrepareCommand(command, null, cmdText, p);
                SQLiteDataReader reader = command.ExecuteReader();
                return reader;
            }
            catch (SQLiteException ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region 返回结果集中的第一行第一列，忽略其他行或列
        /// <summary>
        /// 返回结果集中的第一行第一列，忽略其他行或列
        /// </summary>
        /// <param name="cmdText">要执行的sql</param>
        /// <param name="p">sql的参数,无则设置为null</param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, SQLiteParameter[] p)
        {
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    PrepareCommand(cmd, null, cmdText, p);
                    return cmd.ExecuteScalar();
                }
            }
            catch (SQLiteException ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region ExecutePager分页
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="recordCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="cmdText"></param>
        /// <param name="countText"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, SQLiteParameter[] p)
        {
            DataSet ds = new DataSet();
            try
            {
                if (recordCount < 0)
                    recordCount = int.Parse(ExecuteScalar(countText, p).ToString());

                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, null, cmdText, p);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
                }
            }
            catch (SQLiteException ex)
            {
                ds = null;
                throw ex;
            }
            return ds;
        }
        #endregion

        #region 对Datatable修改的数据反馈到数据库中

        /// <summary>
        /// 对Datatable修改的数据反馈到数据库中
        /// </summary>
        /// <param name="_dt">Datatable</param>
        /// <param name="TableName">数据库中对应的表名</param>
        /// <returns></returns>
        public static bool DatatableToDataBase(DataTable _dt, string TableName)
        {
            bool Result = false;
            try
            {
                string SelectQuery = String.Format("select * from {0} where 1=2", TableName);
                SQLiteDataAdapter datp = new SQLiteDataAdapter(SelectQuery, Connection);
                SQLiteCommandBuilder cmd = new SQLiteCommandBuilder(datp);
                datp.Fill(_dt);
                datp.Update(_dt);
                Result = true;
            }
            catch (System.Exception ex)
            {
                Result = false;
                throw (ex);
            }
            return Result;
        }
        #endregion
    }
        
}
